package com.dfbz.dao;

import com.dfbz.common.Constant;
import com.dfbz.dao.base.BaseDao;
import com.dfbz.model.User;
import com.dfbz.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDao extends BaseDao<User> {


    public UserDao() {
        super(User.class);
    }

    /**
     * 根据用户名关联查询用户基本信息
     *
     * @param username 用户名
     * @return Map对象 包含有头像，部门名称，真实姓名
     */
    public Map<String, Object> getBaseInfoByName(String username) {
        Map<String, Object> map = null;
        String sql = "SELECT u.id as user_id,u.username, ui.real_name, d.`name` as dept_name,ui.pic" +
                "FROM user u" +
                "LEFT JOIN user_info ui on u.id = ui.user_id" +
                "LEFT JOIN dept d on u.dept_id = d.id" +
                "WHERE username = ? ";
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            map = qr.query(sql, new MapHandler(), username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public Map<String, Object> getUserInfoById(String userId) {
        Map<String, Object> map = null;
        String sql = "SELECT d.`name` as dept_name,u.*,ui.*, " +
                "(SELECT COUNT(*) as total FROM user_focus WHERE user_focus_id = ?) as total_focus" +
                "FROM user u" +
                "LEFT JOIN user_info ui on ui.user_id = u.id" +
                "LEFT JOIN dept d on d.id = u.dept_id" +
                "WHERE u.id = ?";
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            map = qr.query(sql, new MapHandler(), userId, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public List<Map<String, Object>> findUsers(String userId, int currentPage, String name) {
        String sql = "SELECT ui.user_id, ui.real_name, ui.age, ui.gender, ui.`describe`,\n" +
                "(SELECT id FROM user_focus WHERE user_id = ? AND user_focus_id = user_id) as fid\n" +
                "FROM user_info ui\n" +
                "LIMIT ?,?";
        if (null != name) {
            sql = "SELECT ui.user_id, ui.real_name, ui.age, ui.gender, ui.`describe`,\n" +
                    "(SELECT id FROM user_focus WHERE user_id = ? AND user_focus_id = user_id) as fid\n" +
                    "FROM user_info ui\n" +
                    "where ui.real_name like ?\n" +
                    "LIMIT ?,?";
        }
        List<Map<String, Object>> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null==name){
                list = qr.query(sql, new MapListHandler(), userId, (currentPage - 1) * Constant.PAGE_SIZE, Constant.PAGE_SIZE);
            }else {
                list = qr.query(sql, new MapListHandler(), userId, name + "%", (currentPage - 1) * Constant.PAGE_SIZE, Constant.PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public long getUserTotal(String name){
        long total = 0L;
        String sql = "select count(*) as totalUser from user_info";
        if (null != name){
            sql = "select count(*) as totalUser from user_info where real_name like ?";
        }
        Map<String, Object> map = null;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null == name) {
                map = qr.query(sql, new MapHandler());
            } else {
                map = qr.query(sql, new MapHandler(), name + "%");
            }
            map = qr.query(sql, new MapHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (null != map) {
            total = Long.parseLong(map.get("totalUser").toString());
        }
        return total;
    }


}
